/***
This do-file produces the Population State-level data we use from the CSV downloaded 
from Census.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Create required folders
cap mkdir "${root}/data/derived/ACS 2014-2018 5-Year State"
cap mkdir "${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables"

*-------------------------------------------------------------------------------
* Load and clean raw data]
*-------------------------------------------------------------------------------

project, uses("${root}/data/dvc/ACS 2014-2018 5-Year State/Population/ACSDP1Y2018.DP05-2023-02-27T205858.csv") 
import delimited "${root}/data/dvc/ACS 2014-2018 5-Year State/Population/ACSDP1Y2018.DP05-2023-02-27T205858.csv", varnames(1) clear

* Remove margin of error columns
drop *marginoferror *percent *percent*

* Keep relevant row: total population
keep if _n == 2
rename  *grouping pop

* Adjust format to long
rename (*estimate) (estimate*)
reshape long estimate, i(pop) j(state) string
drop pop

* Change variable type
replace estimate = subinstr(estimate, ",", "",.)
destring estimate, replace

replace state = proper(state)
replace state = "District of Columbia" if state == "Districtofcolumbia"
replace state = "Puerto Rico" if state == "Puertorico"
replace state = "Rhode Island" if state == "Rhodeisland"
replace state = "South Carolina" if state == "Southcarolina"
replace state = "North Carolina" if state == "Northcarolina"
replace state = "West Virginia" if state == "Westvirginia"
replace state = "New Jersey" if state == "Newjersey"
replace state = "North Dakota" if state == "Northdakota"
replace state = "South Dakota" if state == "Southdakota"
replace state = "New York" if state == "Newyork"
replace state = "New Hampshire" if state == "Newhampshire"
replace state = "New Mexico" if state == "Newmexico"
replace state = "West Virginia" if state == "Westvirginia"

* Drop Puerto Rico and get fips 
drop if state == "Puerto Rico"
statastates, name(state) 
assert _merge == 3
drop _merge 

* Clean
rename estimate pop_2014_2018
rename state state_name

*-------------------------------------------------------------------------------
* Save file
*-------------------------------------------------------------------------------

save "${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables/ACS 2014-2018 Population.dta", replace
project, creates("${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables/ACS 2014-2018 Population.dta")
